Table & views

MySQL CREATE TABLE


A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:


1. Name of the table

2. Names of fields

3. Definitions for each field


Syntax


mysql> CREATE TABLE employee_table(
id int NOT NULL AUTO_INCREMENT,
name varchar(45) NOT NULL,
occupation varchar(35) NOT NULL,
age int NOT NULL,
PRIMARY KEY (id)
);





Command


mysql> DESCRIBE employee_table;





MySQL ALTER Table


The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.


The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the situation.


1) ADD a column in the table


Syntax


ALTER TABLE table_name ADD new_column_name column_definition [ FIRST | AFTER column_name ];

Example


ALTER TABLE cus_tbl
ADD cus_age varchar(40) NOT NULL;




See the recently added columns:


SELECT* FROM cus_tbl;



2) Add multiple columns in the table


Syntax


ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],

Example


ALTER TABLE cus_tbl
ADD cus_address varchar(100) NOT NULL
AFTER cus_surname,
ADD cus_salary int(100) NOT NULL
AFTER cus_age ;




See the recently added columns:


SELECT* FROM cus_tbl;



3). MODIFY column in the table


Syntax

The MODIFY command is used to change the column definition of the table.


ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ];

Example


ALTER TABLE cus_tbl
MODIFY cus_surname varchar(50) NULL;




See the Structure:





4) DROP column in table


The DROP COLUMN command is used to delete a column in an existing table.


Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example


ALTER TABLE cus_tbl
DROP COLUMN cus_address;




See the Structure:





5) RENAME column in table


The RENAME COLUMN and CHANGE statements both allow for the names of existing columns to be altered. The difference is that the CHANGE clause can also be used to alter the data types of a column.


Syntax

ALTER TABLE table_name
CHANGE COLUMN old_name new_name
column_definition
[ FIRST | AFTER column_name ]

Example


ALTER TABLE cus_tbl
CHANGE COLUMN cus_surname cus_title
varchar(20) NOT NULL;



6) RENAME table


In some situations, database administrators and users want to change the name of the table in the SQL database because they want to give a more relevant name to the table. Any database user can easily change the name by using the RENAME TABLE and ALTER TABLE statement in Structured Query Language.


Syntax

ALTER TABLE table_name
RENAME TO new_table_name;

Example


ALTER TABLE cus_tbl
RENAME TO cus_table;




See the Structure:







MySQL Show/List Tables


DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will use the DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command.



Syntax


mysql> SHOW TABLE tablename;

Example


mysql> SHOW TABLES;
OR
mysql> SHOW TABLES FROM mystudentdb;
OR,
mysql> SHOW TABLES IN mystudentdb;






MySQL Rename Table


Sometimes our table name is non-meaningful, so it is required to rename or change the name of the table. MySQL provides a useful syntax that can rename one or more tables in the current database.



Syntax


mysql> RENAME old_table TO new_table;

Example


mysql> RENAME employee TO customer;






MySQL TRUNCATE Table


The TRUNCATE statement in MySQL removes the complete data without removing its structure. It is a part of DDL or data definition language command. Generally, we use this command when we want to delete an entire data from a table without removing the table structure.


The TRUNCATE command works the same as a DELETE command without using a WHERE clause that deletes complete rows from a table. However, the TRUNCATE command is more efficient as compared to the DELETE command because it removes and recreates the table instead of deleting single records one at a time. Since this command internally drops the table and recreates it, the number of rows affected by the truncate statement is zero, unlike the delete statement that returns the number of deleted rows.


Syntax


TRUNCATE [TABLE] table_name;

Example


mysql> TRUNCATE TABLE customer;






MySQL DESCRIBE TABLE


DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will use the DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command. Both DESCRIBE and DESC command are equivalent and case sensitive.



Syntax


{DESCRIBE | DESC} table_name;

Example


mysql> DESCRIBE customer;






MySQL DROP Table


The DROP TABLE statement allows a table to be removed from a MySQL database. This statement deletes the entire structure as well as the content of the table.



Syntax


mysql> DROP TABLE table_name;
OR,
mysql> DROP TABLE schema_name.table_name;

Example


mysql> DROP TABLE orders;






MySQL Temporary Table


A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non- TEMPORARY table of the same name.



Syntax


mysql> CREATE TEMPORARY TABLE table_name


Example


mysql> CREATE TEMPORARY TABLE Students;



MySQL Copy/Clone/Duplicate Table


MySQL copy or clone table is a feature that allows us to create a duplicate table of an existing table, including the table structure, indexes, constraints, default values, etc. Copying data of an existing table into a new table is very useful in a situation like backing up data in table failure.



Syntax


CREATE TABLE new_table_name
SELECT column1, column2, column3
FROM existing_table_name
WHERE condition;

Example


CREATE TABLE IF NOT EXISTS duplicate_table
SELECT * FROM original_table;






MySQL Add/Delete Column


A column is a series of cells in a table that may contain text, numbers, and images. Every column stores one value for each row in a table. In this section, we are going to discuss how to add or delete columns in an existing table.


How can we add a column in MySQL table?

MySQL allows the ALTER TABLE ADD COLUMN command to add a new column to an existing table. The following are the syntax to do this:


Syntax


ALTER TABLE table_name
ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

Example


ALTER TABLE Test
ADD COLUMN Phone_number VARCHAR(20) NOT NULL AFTER Name;



Let us add some data into the Test table using the INSERT statement as follows:


INSERT INTO Test( Name, Phone_number, City)
VALUES ('Peter', '34556745362', 'California'),
('Mike', '983635674562', 'Texas');



How can we drop a column from MySQL table?

Sometimes, we want to remove single or multiple columns from the table. MySQL allows the ALTER TABLE DROP COLUMN statement to delete the column from the table. The following are the syntax to do this:


Syntax


ALTER TABLE table_name DROP COLUMN column_name;

Example


ALTER TABLE Test DROP COLUMN Branch;






MySQL Show Columns


SHOW COLUMNS statement in MySQL is a more flexible way to display the column information in a given table. It can also support views. Using this statement, we will get only that column information for which we have some privilege.



Syntax


SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} table_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

Example


mysql> SHOW COLUMNS FROM student_info;






MySQL Rename Column


Sometimes our column name is non-meaningful, so it is required to rename or change the column's name. MySQL provides a useful syntax that can rename one or more columns in the table. Few privileges are essential before renaming the column, such as ALTER and DROP statement privileges.


MySQL can rename the column name in two ways:


1. Using the CHANGE statement

2. Using the RENAME statement


Using the CHANGE Statement:

Syntax


ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name Data Type;

Example


mysql> ALTER TABLE balance
CHANGE COLUMN account_num account_no VARCHAR(25);



Using the RENAME Statement:

Syntax


ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

Example


mysql> ALTER TABLE customer RENAME COLUMN account to account_no;






MySQL Rename Column


In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.



Syntax


CREATE [OR REPLACE] VIEW view_name AS
SELECT columns
FROM tables
[WHERE conditions];


Example


CREATE VIEW trainer AS
SELECT course_name, trainer
FROM courses;



To see created view:

Syntax


SELECT * FROM view_name;

Example


SELECT * FROM trainer;